﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class BillDetailDB : DBAccess
    {
        public IList<BillDetail> getAll(Bill bill)
        {
            SqlDataReader rd;
            IList<BillDetail> _listBillDetail = new List<BillDetail>();
            if (bill == null)
            {
                rd = ExecuteQuery("Sp_SelectBillDetail", CommandType.StoredProcedure, "@BillId", bill.BillId, "@TableId", bill.Table.TableId, "@Option", 0);
            }
            else
            {
                rd = ExecuteQuery("Sp_SelectBillDetail", CommandType.StoredProcedure, "@BillId", bill.BillId, "@TableId", bill.Table.TableId, "@Option", 1);
            }
            while (rd.Read())
            {
                Bill _bill = new Bill();
                Product _product = new Product();
                BillDetail _billDetail = new BillDetail();
                _billDetail.Quantity = int.Parse(rd["Quantity"].ToString());
                _billDetail.Price = int.Parse(rd["Price"].ToString());
                _bill.BillId = int.Parse(rd["BillId"].ToString());
                _billDetail.Bill = _bill;
                _product.ProductId = int.Parse(rd["ProductId"].ToString());
                _product.Name = rd["ProductName"].ToString();
                _billDetail.Product = _product;

                _listBillDetail.Add(_billDetail);

            }
            rd.Close();
            return _listBillDetail;
        }

        public void AddProductToBIll(Product _product, Bill _bill, int quantity, decimal Price)
        {
            string sql = string.Format("insert into BillDetail(BillId,ProductId,Quantity,Price) values({0},{1},{2},{3})", _bill.BillId, _product.ProductId, quantity, Price);
            this.ExecuteNonQuery(sql, CommandType.Text);

        }

        public void AddProductToBIll(BillDetail fbilldetail, Bill fbill)
        {
            string sql = string.Format("insert into BillDetail(BillId,ProductId,Quantity,Price) values({0},{1},{2},{3})", fbill.BillId, fbilldetail.Product.ProductId, fbilldetail.Quantity, fbilldetail.Price);
            this.ExecuteNonQuery(sql, CommandType.Text);

        }

        public void RemoveProductFromBill(Product _product, Bill _Bill)
        {
            string sql = string.Format("delete from BillDetail where BillId={0} and productId={1}", _Bill.BillId, _product.ProductId);
            ExecuteNonQuery(sql, CommandType.Text);
        }

        public void UpdateProductToBill(Product _product, Bill _bill, int quantity)
        {
            string sql = string.Format("update BillDetail set quantity={0} where productId={1} and BillId={2}", quantity, _product.ProductId, _bill.BillId);
            this.ExecuteNonQuery(sql, CommandType.Text);
        }

        /// <summary>
        /// return Bill of table
        /// </summary>
        /// <param name="TableId"></param>
        /// <returns></returns>
        public Bill GetBillFromTableId(int TableId)
        {
            Bill _bill = null;
            int tableExtend = 0;
            string sql = "SELECT * FROM Bill WHERE (status=1 or status=2) AND TableId=" + TableId.ToString();
            SqlDataReader rd = this.ExecuteQuery(sql, CommandType.Text);
            if (rd.Read())
            {
                _bill = new Bill();
                _bill.BillId = int.Parse(rd["BillId"].ToString());
                string _sdateTime = rd["DateCreate"].ToString();
                DateTime _datetime = Convert.ToDateTime(_sdateTime);
                _bill.Datecreate = _datetime;
                _bill.Status = (BillStatus)(int.Parse(rd["status"].ToString()));
                int.TryParse(rd["tableextend"].ToString(), out tableExtend);
                _bill.TableExtent = tableExtend;
                rd.Close();
                return _bill;
            }
            rd.Close();
            return _bill;

        }

        public int GetQuantityFromList(int ProductId, IList<BillDetail> _listBillDetail)
        {
            if (_listBillDetail == null)
                return -1;
            foreach (BillDetail item in _listBillDetail)
            {
                if (item.Product.ProductId == ProductId)
                    return item.Quantity;
            }
            return -1;
        }

        public bool checkBillDetail(Bill _bill, Product _product, IList<BillDetail> _listBillDetail)
        {
            if (_listBillDetail == null)
                return false;
            foreach (BillDetail item in _listBillDetail)
            {
                if (item.Product.ProductId == _product.ProductId && item.Bill.BillId == _bill.BillId)
                    return true;
            }
            return false;
        }

        /// <summary>
        /// Payed and set status = 2 
        /// </summary>
        /// <param name="_bill"></param>
        public void PayTheBill(Bill _bill)
        {
            string sql = "UPDATE Bill SET status=2 WHERE BillId = " + _bill.BillId.ToString();
            ExecuteNonQuery(sql, CommandType.Text);
        }

        public void RemoveAllproductFromBill(Bill fbill)
        {
            string query = "delete from BillDetail where BillId=" + fbill.BillId.ToString();
            ExecuteNonQuery(query, CommandType.Text);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fbill"></param>
        /// <returns></returns>
        public DataView GetDataviewPrint(Bill fbill)
        {
            string sql = "SELECT Products.Name, BillDetail.Quantity,BillDetail.Price, BillDetail.Quantity * dbo.BillDetail.Price AS TotalPrice FROM BillDetail INNER JOIN  Bill ON BillDetail.BillId = Bill.BillId INNER JOIN Products ON BillDetail.ProductId = Products.ProductId  WHERE (dbo.BillDetail.BillId = " + fbill.BillId.ToString() + ")";
            
            DataView dv = new DataView(ExecuteQueryTable(sql, CommandType.Text));
            return dv;
        }
    }
}
